package cn.ctbu.xiaopeng.studentscoremanage.dao;

import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.OperateLogDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.OperateLog;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.OperateLogVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;

/**
 * 操作日志的信息管理-使用AOP切面类LogAspect
 *
 * @author xiaopeng
 * @version 1.0
 */
public interface OperateLogRepository extends JpaRepository<OperateLog, Integer> {

    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.OperateLogVO(" +
            "o.id, o.operateUserId, o.operateTime, o.className, o.methodName, " +
            "o.methodParams, o.returnValue, o.costTime) " +  // 将OperateLog字段映射到OperateLogVO
            "FROM tb_operate_log o " +  // 使用OperateLog实体
            "WHERE (:#{#operateLogDTO.operateUserId} IS NULL OR o.operateUserId LIKE %:#{#operateLogDTO.operateUserId}%) " +
            "AND (:#{#operateLogDTO.className} IS NULL OR o.className LIKE %:#{#operateLogDTO.className}%) " +
            "AND (:#{#operateLogDTO.methodName} IS NULL OR o.methodName LIKE %:#{#operateLogDTO.methodName}%) " +
            "AND (:#{#operateTimeBegin} IS NULL OR o.operateTime >= :#{#operateTimeBegin}) " +
            "AND (:#{#operateTimeEnd} IS NULL OR o.operateTime <= :#{#operateTimeEnd})")
    Page<OperateLogVO> getOperateLogsByConditions(@Param("operateLogDTO") OperateLogDTO operateLogDTO,
                                                  @Param("operateTimeBegin") LocalDateTime operateTimeBegin,
                                                  @Param("operateTimeEnd") LocalDateTime operateTimeEnd,

                                                  Pageable pageable);

    /**
     * 保存操作日志
     *
     * @param operateLog
     * @return
     */
    OperateLog save(OperateLog operateLog);

    /**
     * 删除最早的 100 条日志记录
     */

    // 使用原生 SQL 删除最早的 100 条日志记录
    @Modifying
    @Transactional
    @Query(value = "DELETE o FROM tb_operate_log o " +
            "JOIN (SELECT id FROM tb_operate_log ORDER BY operate_time ASC LIMIT 100) AS subquery " +
            "ON o.id = subquery.id", nativeQuery = true)
    void deleteEarliest100Logs();


    // 使用原生 SQL 删除最早的 5 条日志记录
    @Modifying
    @Transactional
    @Query(value = "DELETE o FROM tb_operate_log o " +
            "JOIN (SELECT id FROM tb_operate_log ORDER BY operate_time ASC LIMIT 5) AS subquery " +
            "ON o.id = subquery.id", nativeQuery = true)
    void deleteEarliest5Logs();


}
